CREATE OR REPLACE PROCEDURE USP_SYNC_EVENT AS
Begin
    declare
       idCount Number(10);
       appCount number(10);
    begin
    for e in (select a.id, a.event_id,a.app_code,a.app_name,a.app_level from tb_event_temp a for update) loop

        --update event
        select count(*) into idCount from tb_event where event_id=e.event_id;
        if idCount>0 then
          Update Tb_Event A
          Set (A.Event_Level,A.Ticket_Id,A.Event_Source,A.Host_Id,A.Begin_Time,
               A.Event_Desc,A.Status,A.App_Code,A.App_Level,A.App_Name,Update_Date)
          =
          (Select B.Event_Level,B.Ticket_Id,B.Event_Source,B.Host_Id,B.Begin_Time,
                  b.event_desc,b.status,b.app_code,b.app_level,b.app_name,sysdate
           from tb_event_temp b where b.id=e.id
          )
          Where a.event_id=e.event_id
          ;
        else
          INSERT INTO TB_EVENT ( event_id,EVENT_LEVEL,TICKET_ID, EVENT_SOURCE, HOST_ID, BEGIN_TIME, EVENT_DESC, STATUS, APP_NAME, APP_CODE,App_Level,Sync_Date,Update_Date )
          Select
              event_id,EVENT_LEVEL,TICKET_ID, EVENT_SOURCE, HOST_ID, BEGIN_TIME, EVENT_DESC, STATUS, APP_NAME, APP_CODE,App_Level,sysdate,sysdate
          From Tb_Event_Temp A
          where a.event_id=e.event_id and rownum=1
          ;
        end if;

        --update application
        if e.app_code is not null then

        select count(*) into appCount from tb_application t
        where t.app_code=e.app_code;
        if appCount>0 then
           update tb_application a
           set
           a.app_code=e.app_code,
           a.app_name=e.app_name,
           a.app_type=e.app_level,
           a.update_date=sysdate
           where a.app_code=e.app_code and (a.app_name!=e.app_name or nvl(a.app_type,'0')!=e.app_level)
           ;
        else
           insert into tb_application( app_code,app_name,app_type,sync_date,update_date)
           values(e.app_code,e.app_name,e.app_level,sysdate,sysdate)
           ;
        end if;
        end if;

        delete from tb_event_temp where id=e.id;
    end loop;
    end;

    Commit;
    Exception
      When Others Then
      Rollback;
      usp_insert_synclog( P_LINE_NUMBER => SQLERRM, P_ERR_CODE => SQLCODE, P_ERR_MESSAGE => 'USP_SYNC_EVENT error' );


END USP_SYNC_EVENT;
/
